﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Threading.Tasks;
namespace Helper
{
    public class DataAccessHelper
    {
        static OleDbConnection oleConnection = null;

        public static OleDbConnection OleConnection
        {
            get
            {
                try
                {
                    if ((oleConnection == null) || (oleConnection.State == ConnectionState.Closed) || (oleConnection.State == ConnectionState.Broken))
                    {
                        oleConnection = new OleDbConnection(ConnectionStringHelper.ConnectionString);
                        oleConnection.Open();


                        return oleConnection;
                    }
                    else if (oleConnection.State == ConnectionState.Connecting)
                    {
                        while (oleConnection.State == ConnectionState.Connecting)
                        { }
                        return oleConnection;
                    }
                    else
                        return oleConnection;
                }
                catch { return oleConnection; }
            }

        }

        internal static void RefreshConnection()
        {
            try
            {
                if (oleConnection != null)
                    oleConnection.Dispose();
            }
            catch { }
        }

        public static bool RestoreDefaultDbBackup(string pathToCompressedFile, string dPassword)
        {
            bool succ = false;
            string pathToNewFile = FileHelper.GetNewDefBackupPath();
            succ = FileHelper.Decompress(pathToCompressedFile, pathToNewFile);
            if (!succ)
                return false;
            string commandText = "USE master\r\n" +
                "ALTER DATABASE [Starehe] SET AUTO_UPDATE_STATISTICS OFF";
            string commandTextx = "USE master\r\nALTER DATABASE [Starehe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
            string commandText2 =
                 "USE Master\r\nRESTORE DATABASE [Starehe] FROM  DISK = N'" + pathToNewFile + "'" +
                 " WITH  FILE = 1, PASSWORD = N'" + dPassword + "',  NOUNLOAD,  REPLACE, " +
                 " STATS = 10";
            string commandText3 =
                "USE master\r\nALTER DATABASE [Starehe] SET MULTI_USER WITH NO_WAIT";
            string commandText4 = "USE master\r\n" +
                "ALTER DATABASE [Starehe] SET AUTO_UPDATE_STATISTICS ON";
            try
            {
                using (OleDbConnection sqlConn = new OleDbConnection(ConnectionStringHelper.ConnectionString))
                {
                    sqlConn.Open();
                    OleDbCommand sqlcmd = new OleDbCommand(commandText, sqlConn);
                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.CommandText = commandTextx;
                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.CommandText = commandText2;
                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.CommandText = commandText3;
                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.CommandText = commandText4;
                    sqlcmd.ExecuteNonQuery();
                    sqlConn.Close();
                    FileHelper.DeleteFile(pathToNewFile);
                }
                succ = true;
            }
            catch
            {
                try
                {
                    ExecuteNonQuery(commandText4,false);
                }
                catch { }
                succ = false;
            }
            return succ;
        }
        
        public static bool CreateDefaultDbBackup(string filePath)
        {        
            string defBk = FileHelper.GetNewDefBackupPath();

            string commandText =
                "BACKUP DATABASE [Starehe] TO  DISK = N'" + defBk + "'" +
                " WITH NOFORMAT,NOINIT, NAME = N'Starehe-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, " +
                " STATS = 10";
            try
            {
                bool succ = true && ExecuteNonQuery(commandText, false);
                succ = succ && FileHelper.Compress(defBk, filePath) &&
                    FileHelper.DeleteFile(defBk);
                return succ;
            }
            catch { return false; }
        }

        private static int GetNewID(string nameOfTable)
        {
            string selectStr = "declare @newid int;  set @newid =dbo.GetNewID('" + nameOfTable
                + "');  select @newid;";

            string finalStr = ExecuteScalar(selectStr);
            int res;
            int.TryParse(finalStr, out res);
            return res;
        }

        public static Task<int> GetNewIDAsync(string nameOfTable)
        {
            return Task.Run<int>(() =>
            {
                return GetNewID(nameOfTable);
            });
        }

        
        public static object ExecuteObjectScalar(string commandText)
        {
            return ExecuteObjectScalar(commandText, true);
        }

        public static string ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, true);
        }

        public static string ExecuteScalar(string commandText,bool hasHeader)
        {
            object res = ExecuteObjectScalar(commandText, hasHeader);
            if (res != null)
                return res.ToString();
            else return "";
        }

        public static object ExecuteObjectScalar(string commandText, bool hasHeader)
        {
            try
            {
                if (hasHeader)
                    commandText = "USE Starehe\r\nSET DATEFORMAT DMY\r\n" + commandText;

                OleDbCommand sqlcmd = new OleDbCommand(commandText, OleConnection);

                object tx = sqlcmd.ExecuteScalar();
                
                sqlcmd.Dispose();
                return tx;
            }
            catch (Exception e)
            {                
                throw e; 
            }
        }
       
        public static ObservableCollection<string> CopyFromDBtoObservableCollection(string selectString)
        {
            try
            {
                ObservableCollection<string> top50List = new ObservableCollection<string>();
                DataTable dtSet = DataAccessHelper.ExecuteNonQueryWithResultTable(selectString);
                if (dtSet != null)
                {
                    for (int i = 0; i < dtSet.Rows.Count; i++)
                    {
                        top50List.Add(dtSet.Rows[i][0].ToString());
                    }
                    return top50List;
                }
            }
            catch 
            {               
            }
            return new ObservableCollection<string>();
        }

        public static DataTable ExecuteNonQueryWithParametersWithResultTable(string commandText,
            ObservableCollection<OleDbParameter> paramColl)
        {
            DataTable result = new DataTable();

            try
            {
                OleDbCommand dtab = new OleDbCommand();
                dtab.CommandText = "USE Starehe\r\nSET DATEFORMAT DMY\r\n" + commandText;
                dtab.Connection = OleConnection;
                foreach (OleDbParameter param in paramColl)
                { dtab.Parameters.Add(param); }
                OleDbDataAdapter dta = new OleDbDataAdapter(dtab);

                DataSet dtSet = new DataSet();
                dta.Fill(dtSet);

                dta.Dispose();
                result = dtSet.Tables[0];
            }
            catch (Exception e)
            {
                throw e;
            }
            return result;
        }

        public static bool ExecuteNonQueryWithParameters(string commandText, ObservableCollection<OleDbParameter> paramColl)
        {
            bool result = false;

            try
            {
                OleDbCommand dta = new OleDbCommand();
                dta.CommandText = "USE Starehe\r\nSET DATEFORMAT DMY\r\n" + commandText;
                dta.Connection = OleConnection;
                foreach (OleDbParameter param in paramColl)
                { dta.Parameters.Add(param); }
                dta.ExecuteNonQuery();

                dta.Dispose();
                result = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            return result;
        }

        public static DataTable ExecuteNonQueryWithResultTable(string commandText)
        {
            return ExecuteNonQueryWithResultTable(commandText, true);
        }

        public static DataTable ExecuteNonQueryWithResultTable(string commandText, bool hasHeader)
        {
            DataTable result = new DataTable();
            if (hasHeader)
                commandText = "USE Starehe\r\nSET DATEFORMAT DMY\r\n" + commandText;
            try
            {
                OleDbDataAdapter dta = new OleDbDataAdapter(commandText, OleConnection);

                DataSet dtSet = new DataSet();
                dta.Fill(dtSet);

                result = dtSet.Tables[0];
                dta.Dispose();
                    
            }
            catch (Exception e)
            {
                throw e;
            }
            return result;
        }

        public static bool ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, true);
        }

        public static bool ExecuteNonQuery(string commandText, bool hasHeader)
        {
            bool succ = false;
            if (hasHeader)
                commandText = "USE Starehe\r\nSET DATEFORMAT DMY\r\n" + commandText;

            try
            {
                OleDbCommand dta = new OleDbCommand(commandText, OleConnection);
                dta.ExecuteNonQuery();
                succ = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            return succ;

        }
        
    }
}
